//
//  db.m
//  SrnTeacher
//
//  Created by iDeneb on 5/5/10.
//  Copyright 2010 __MyCompanyName__. All rights reserved.
//

#import "db.h"
#import "file.h"
#import "Stat.h"
#import "User.h"
@implementation db

-(BOOL) open{
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"tea.sql"];
    NSFileManager *fileManager = [NSFileManager defaultManager];
    BOOL find = [fileManager fileExistsAtPath:path];
	
    //找到数据库文件mydb.sql
    if (find) {
        NSLog(@"Database file have already existed.");
        if(sqlite3_open([path UTF8String], &database_) != SQLITE_OK) {
            sqlite3_close(database_);
            NSLog(@"Error: open database file.");
            return NO;
        }
        return YES;
    }
    if(sqlite3_open([path UTF8String], &database_) == SQLITE_OK) {
        //bFirstCreate_ = YES;
        [self createUserTable:database_];
		
        return YES;
    } else {
        sqlite3_close(database_);
        NSLog(@"Error: open database file.");
        return NO;
    }
    return NO;
}

- (BOOL) createUserTable:(sqlite3*)db{
    char *sql = "CREATE TABLE user (uid int, \
	username text, \
	num int, \
	choice text, \
	flag  int)";
    sqlite3_stmt *statement;
    if(sqlite3_prepare_v2(db, sql, -1, &statement, nil) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement:create user table");
        return NO;
    }
    int success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    if ( success != SQLITE_DONE) {
        NSLog(@"Error: failed to dehydrate:CREATE TABLE user");
        return NO;
    }
    NSLog(@"Create table 'user' successed.");
    return YES;
}

-(BOOL) insertOneChoice:(User*)user {
    sqlite3_stmt *statement;
    static char *sql = "INSERT INTO user (uid,username,num,choice,flag) VALUES(?,?,?,?,?)";
	
    //问号的个数要和(cid,title,imageData,imageLen)里面字段的个数匹配，代表未知的值，将在下面将值和字段关联。
    int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to insert:choice");
        return NO;
    }
	
	
	//这里的数字1，2，3，4.5代表第几个问号
    sqlite3_bind_int(statement, 1, user.uid );
    sqlite3_bind_text(statement, 2, [user.username UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(statement, 3, user.num);
    sqlite3_bind_text(statement, 4, [user.choice UTF8String], -1, SQLITE_TRANSIENT); 
	int flag = 1;
	if(user.num == 0) {
		flag = 0;
	}
	sqlite3_bind_int(statement, 5, flag);	
	
    success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    
    if (success == SQLITE_ERROR) {
        NSLog(@"Error: failed to insert into the database with message.");
        return NO;
    } 
	
	//NSLog(@"Insert One user#############:uid = %@",user.uid);
    return YES;
}

-(BOOL)updateFlag:(int)num{
	sqlite3_stmt *statement;
    static char *sql = "update user set flag=1 where num=?";
	
    //问号的个数要和(cid,title,imageData,imageLen)里面字段的个数匹配，代表未知的值，将在下面将值和字段关联。
    int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to update:choice");
        return NO;
    }
	
    sqlite3_bind_int(statement, 1, num);
	
    success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    
    if (success == SQLITE_ERROR) {
        NSLog(@"Error: failed to update into the database with message.");
        return NO;
    } 
	
	//NSLog(@"Insert One user#############:uid = %@",user.uid);
    return YES;	
}


-(BOOL)deleteOneQuestion:(int)num{
	sqlite3_stmt *statement;
    static char *sql = "delete from user where num=?";
	
    //问号的个数要和(cid,title,imageData,imageLen)里面字段的个数匹配，代表未知的值，将在下面将值和字段关联。
    int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to update:choice");
        return NO;
    }
	
    sqlite3_bind_int(statement, 1, num);
	
    success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    
    if (success == SQLITE_ERROR) {
        NSLog(@"Error: failed to delete the database with message.");
        return NO;
    } 
	
	//NSLog(@"Insert One user#############:uid = %@",user.uid);
    return YES;	
}


-(BOOL)deleteOneAsk:(NSString *)name  choiceStr:(NSString *)choice {
	sqlite3_stmt *statement;
    static char *sql = "delete from user where username=? and choice=? and num=-1";
	
    //问号的个数要和(cid,title,imageData,imageLen)里面字段的个数匹配，代表未知的值，将在下面将值和字段关联。
    int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to update:choice");
        return NO;
    }
	
   sqlite3_bind_text(statement, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
   sqlite3_bind_text(statement, 2, [choice UTF8String], -1, SQLITE_TRANSIENT);	
	
    success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    
    if (success == SQLITE_ERROR) {
        NSLog(@"Error: failed to delete the database with message.");
        return NO;
    } 
	
	//NSLog(@"Insert One user#############:uid = %@",user.uid);
    return YES;	
}

-(BOOL)delete {
	sqlite3_stmt *statement;
    static char *sql = "delete from user";
	
    //问号的个数要和(cid,title,imageData,imageLen)里面字段的个数匹配，代表未知的值，将在下面将值和字段关联。
    int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to insert:channels");
        return NO;
    }
	
    success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    
    if (success == SQLITE_ERROR) {
        NSLog(@"delete from user error");
        return NO;
    } 
	
	//NSLog(@"Insert One user#############:uid = %@",user.uid);
    return YES;	
}


-(int)getMaxId {
	sqlite3_stmt *statement = nil;
    char *sql = "SELECT MAX(num) FROM user";
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get users.");
    }
	int max = 1;

    //查询结果集中一条一条的遍历所有的记录，这里的数字对应的是列值。
    while (sqlite3_step(statement) == SQLITE_ROW) {
         max  = sqlite3_column_int(statement, 0);
         
	}
	
    sqlite3_finalize(statement);
	return max;
}

-(int)getMinId {
	sqlite3_stmt *statement = nil;
    char *sql = "SELECT min(num) FROM user";
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get users.");
    }
	int max = 1;
	
    //查询结果集中一条一条的遍历所有的记录，这里的数字对应的是列值。
    while (sqlite3_step(statement) == SQLITE_ROW) {
		max  = sqlite3_column_int(statement, 0);
		
	}
	
    sqlite3_finalize(statement);
	return max;
}

-(NSMutableArray *)getHisByName:(NSString *)name {
	NSMutableArray *votes=[[NSMutableArray alloc] init];
	sqlite3_stmt *statement = nil;
    char *sql = "SELECT num, choice from user where username = ? and num > 0 order by num";
	
    int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to get view by name");
        return NO;
    }
	 sqlite3_bind_text(statement, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
	
    //查询结果集中一条一条的遍历所有的记录，这里的数字对应的是列值
	int num;
	NSString *choice; 
	while (sqlite3_step(statement) == SQLITE_ROW) {
		num  = sqlite3_column_int(statement, 0);
		char *temp = (char*)sqlite3_column_text(statement, 1);
		if(temp) {
            choice = [NSString stringWithUTF8String:temp];
		} else {
		    choice=@"";
		}
		User *user = [User alloc];
		user.username = name;
		user.choice = choice ;
		user.sock = nil;
		user.removed = NO;
		user.muted = NO;
		user.num = num;
	   [votes addObject: user];
	}
	
    sqlite3_finalize(statement);
	
	return votes;
}

-(int)getQnum {
	sqlite3_stmt *statement = nil; 
    char *sql = "SELECT count( distinct num)  FROM user";
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get users.");
    }
	int max = 0;
	
    //查询结果集中一条一条的遍历所有的记录，这里的数字对应的是列值。
    while (sqlite3_step(statement) == SQLITE_ROW) {
		max  = sqlite3_column_int(statement, 0);
		
	}
	
    sqlite3_finalize(statement);
	return max;
	
}

-(NSMutableArray *)getViewByNum:(int)num {
	NSMutableArray *votes=[[NSMutableArray alloc] init];
	sqlite3_stmt *statement = nil;
    char *sql = "SELECT username, choice from user where  num = ?";
	
    int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to get view data");
        return NO;
    }
	sqlite3_bind_int(statement, 1, num);
	
    //查询结果集中一条一条的遍历所有的记录，这里的数字对应的是列值
	NSString *choice;
	NSString *uname; 
	while (sqlite3_step(statement) == SQLITE_ROW) {
		char *name  = (char *)sqlite3_column_text(statement, 0);
		char *cc  = (char*)sqlite3_column_text(statement, 1);
		if(name) {
            uname = [NSString stringWithUTF8String:name];
		} else {
		    uname=@"";
		}	
		if(cc) {
            choice = [NSString stringWithUTF8String:cc];
		} else {
		    choice=@"";
		}
		
		User *user = [User alloc];
		user.username = uname;
		user.choice = choice ;
		user.sock = nil;
		user.removed = NO;
		user.muted = NO;
		user.num = num;
		[votes addObject: user];
	}
	
    sqlite3_finalize(statement);
	
	return votes;
}

-(NSMutableArray *)getStatByNum:(int)num {
  NSMutableArray *stats=[[NSMutableArray alloc] init];
	sqlite3_stmt *statement = nil;
    char *sql = "SELECT count(*), choice FROM user where num = ? group by choice";
	
    int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to get stats");
        return NO;
    }
	sqlite3_bind_int(statement, 1, num);
	
    //查询结果集中一条一条的遍历所有的记录，这里的数字对应的是列值
	int count;
	NSString *choice;
    while (sqlite3_step(statement) == SQLITE_ROW) {
		count  = sqlite3_column_int(statement, 0);
		char *temp = (char*)sqlite3_column_text(statement, 1);
		if(temp) {
            choice = [NSString stringWithUTF8String:temp];
		} else {
		    choice=@"";
		}
		[stats addObject:[Stat makeWithX:count choiceStr:choice]];
	}
	
    sqlite3_finalize(statement);
	
  return stats;
	
}

-(void) getResults:(NSMutableArray*)users{
	sqlite3_stmt *statement1 = nil;
	char *sql1 = "SELECT distinct  username  FROM user order by username ";
    if (sqlite3_prepare_v2(database_, sql1, -1, &statement1, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement1 with message:get users.");
    }
	
	NSMutableString *ms = [[NSMutableString alloc] initWithCapacity:200];
	NSMutableArray *names = [[NSMutableArray alloc] init];
	
	while (sqlite3_step(statement1) == SQLITE_ROW) {
		[ms appendString:@","];	
		char* username = (char*)sqlite3_column_text(statement1, 0);
		NSString *name = [NSString stringWithFormat:@"%s", username];
		[ms appendString:name];	
		[names addObject:name];
	}
	
	sqlite3_finalize(statement1);
	[ms appendString:@"\n"];
	NSString *namesLine = [NSString stringWithString: ms];	
	sqlite3_stmt *statement = nil;
    char *sql = "SELECT username, num, choice, flag  FROM user  where num > 0 order by num, username";
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get resulte to file.");
    }
	file *testf;
	
	testf = [file alloc];
	[testf deleteFile]; 
	int old = 0;
	int i = 0;
	int oi = 0;
	//first write names
	BOOL re =[testf write:namesLine];
	int flag = 0;
	int len = [names count];
	int tag = 0;
	//[ms  init];
	//[ms release];
    //查询结果集中一条一条的遍历所有的记录，这里的数字对应的是列值。
    while (sqlite3_step(statement) == SQLITE_ROW) {
        
        char* username = (char*)sqlite3_column_text(statement, 0);
        int num  = sqlite3_column_int(statement, 1);
        char* choice   = (char *)sqlite3_column_text(statement, 2); 
		//int tag = sqlite3_column_int(statement, 3);
		//a new question
		if (num != old) {
			if(flag > 0){
				[ms appendString:@"\n"];
				NSString *content = [NSString stringWithString:ms];
				[testf write:content];
			}
	       
			[ms release];
			ms = [[NSMutableString alloc] initWithCapacity:200];
			NSString *cc = [NSString stringWithFormat:@"%d", num];
			[ms appendString:cc];
			i = 0;
			oi = 0;
			tag = 0;
			
		}
		NSString *uu = [NSString stringWithFormat:@"%s", username];
		for ( int j =oi; j < len; j++) {
			NSString *s = [names objectAtIndex:j];
			if ([uu localizedCompare:s] == 0) {
				i = j;
			}
		}
		int t = 0;
		int tmp=1;
		if (oi == 0 && tag ==0 ){
			tag=1;
			tmp=0;
		} 
		while ( t < i - oi - tmp) {
			[ms appendString:@","];
			t++;
		}
		NSString *ch = [NSString stringWithFormat:@",%s", choice];
        [ms appendString:ch];
		
		old = num;
		oi = i;
		flag++;
	}
	
	//last shoule write last line
	[ms appendString:@"\n"];
	NSString *content = [NSString stringWithString:ms];
	[testf write:content];
	sqlite3_finalize(statement);	
	
	sqlite3_stmt *statement3 = nil;
    char *sql3 = "SELECT username, choice FROM user where num=-1";
    if (sqlite3_prepare_v2(database_, sql3, -1, &statement3, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement3 with message:get resulte to file.");
    }
	while (sqlite3_step(statement3) == SQLITE_ROW) {
		char* username = (char*)sqlite3_column_text(statement, 0);
	    char* choice   = (char *)sqlite3_column_text(statement, 1); 
		NSString *uu = [NSString stringWithFormat:@"%s", username];
		int i = 0;
		for ( int j = 0; j < len; j++) {
			NSString *s = [names objectAtIndex:j];
			if ([uu localizedCompare:s] == 0) {
				i = j;
				break;
			}
		}
		[ms release];
		ms = [[NSMutableString alloc] initWithCapacity:200];
	    [ms appendString:@"Ask" ];
		int a = 0;
		while(a < i) {
			[ms appendString:@"," ];
			a++;
		}
		
		NSString *ch = [NSString stringWithFormat:@",%s", choice];
        [ms appendString:ch];
		while(i < len - 1) {
			[ms appendString:@"," ];
			i++;
		}
		[ms appendString:@"\n"];
		NSString *content = [NSString stringWithString:ms];
		[testf write:content];		
	}
	
    sqlite3_finalize(statement3);
	[testf release];
}

-(void)close {
	sqlite3_close(database_);
}
@end
